<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>openGauss Blog  | openGauss1.0.1部分新特性测试</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link rel="shortcut icon" href="https://xzx666.gitee.io/lookeng/img/favicon.ico" type="image/x-icon" />
    <link rel="apple-touch-icon" href="https://xzx666.gitee.io/lookeng/img/apple-touch-icon.png" />

    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

    
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
    
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/commen.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/blog.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/mobile.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/pagination.css?t=1607593672000">
    
    <script>
      var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?34e9f585f29581007941aa1698181871";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
</head>
<body>
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src='//unpkg.com/valine/dist/Valine.min.js'></script>






<div class="container">
    <div class="columns blog-detail">
        <div class="post_detail">
            <div class="is-child box">
                <div class="breadCrumb"><a href="\zh\">博客/</a></div>
                <div class="blog-detail-header">
                    <h1>openGauss1.0.1部分新特性测试</h1>
                    <div class="blog-detail-prop">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-date.png">
                        <span class="article_right_date">2020-11-25</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-author.png">
                        <span class="article_right_author">多米爸比</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-view.png">
                        <span id="/zh/post/duomibabi/opengauss1-0-1%E9%83%A8%E5%88%86%E6%96%B0%E7%89%B9%E6%80%A7%E6%B5%8B%E8%AF%95/" class="leancloud_visitors" data-flag-title="openGauss1.0.1部分新特性测试">
                            <i class="leancloud-visitors-count"></i>                     
                        </span>
                    </div>
                    <div class="blog-detail-tags">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-tag.png">
                        
                        <a href="https://xzx666.gitee.io/lookeng/zh/tags/opengauss%E5%8A%9F%E8%83%BD%E6%B5%8B%E8%AF%95">openGauss功能测试</a>
                        
                    </div>
                </div>
                <div class="content blog-content">
                    

<h1 id="opengauss1-0-1部分新特性测试-a-name-zh-cn-topic-0293240558-a">openGauss1.0.1部分新特性测试<a name="ZH-CN_TOPIC_0293240558"></a></h1>

<h2 id="支持全局临时表-a-name-section43741428104511-a">支持全局临时表<a name="section43741428104511"></a></h2>

<p>PostgreSQL原生不支持全局临时表，需要借助第三方插件。具体使用可参考：<a href="https://www.modb.pro/db/31555">PostgreSQL全局临时表插件pgtt的使用</a></p>

<p>opengauss原生支持全局临时表，下面进行测试。</p>

<p><strong>全局临时表：数据会话级可见</strong></p>

<pre><code>建表语句，使用ON COMMIT PRESERVE ROWS
CREATE GLOBAL  TEMPORARY TABLE test_gtt_table (
	id integer,
	lbl text
) ON COMMIT PRESERVE ROWS;

当前会话插入数据并查询
omm=# insert into test_gtt_table values(1,'data1');
INSERT 0 1
omm=# insert into test_gtt_table values(2,'data2');
INSERT 0 1
omm=# select * from test_gtt_table ;
 id |  lbl  
----+-------
  1 | data1
  2 | data2
(2 rows)

退出会话再查看
omm=# \dt test_gtt_table
                                           List of relations
 Schema |      Name      | Type  | Owner |                           Storage                            
--------+----------------+-------+-------+--------------------------------------------------------------
 public | test_gtt_table | table | omm   | {orientation=row,compression=no,on_commit_delete_rows=false}
(1 row)

omm=# select * from test_gtt_table ;
 id | lbl 
----+-----
(0 rows)
</code></pre>

<p>数据会话级可见，其他会话看不到数据，但表结构可见。</p>

<p><strong>全局临时表：数据事务级可见</strong></p>

<pre><code>omm=# drop table test_gtt_table;
DROP TABLE

建表语句，使用ON COMMIT DELETE ROWS
CREATE GLOBAL  TEMPORARY TABLE test_gtt_table (
	id integer,
	lbl text
) ON COMMIT DELETE ROWS;

mm=# begin;
BEGIN
omm=# insert into test_gtt_table values(1,'data1');
INSERT 0 1
omm=#  select * from test_gtt_table ;
 id |  lbl  
----+-------
  1 | data1
(1 row)

omm=# commit;
COMMIT
omm=# select * from test_gtt_table ;
 id | lbl 
----+-----
(0 rows)
</code></pre>

<p>数据事务级可见，事务提交后数据删除。</p>

<h2 id="支持xml和json类型-a-name-section11621164975110-a">支持xml和json类型<a name="section11621164975110"></a></h2>

<p><strong>测试例子</strong></p>

<pre><code>create table test_xml(j json,x xml);

insert into test_xml(j,x) values('{&quot;key&quot;:&quot;opengauss1.0.0&quot;}','&lt;value&gt;one&lt;/value&gt;');

insert into test_xml(j,x) values('{&quot;key&quot;:&quot;opengauss1.0.1&quot;}','&lt;value&gt;two&lt;/value&gt;');

select * from test_xml;

输出结果
            j             |         x          
--------------------------+--------------------
 {&quot;key&quot;:&quot;opengauss1.0.0&quot;} | &lt;value&gt;one&lt;/value&gt;
 {&quot;key&quot;:&quot;opengauss1.0.1&quot;} | &lt;value&gt;two&lt;/value&gt;
(2 rows)
</code></pre>

<p>使用xml数据类型时需要编译openGauss-server时打开–with-libxml选项，否则使用时会报错：</p>

<pre><code>omm=# insert into test_xml(j,x) values('{&quot;key&quot;:&quot;opengauss1.0.0&quot;}','&lt;value&gt;one&lt;/value&gt;');
ERROR:  unsupported XML feature
LINE 1: ...o test_xml(j,x) values('{&quot;key&quot;:&quot;opengauss1.0.0&quot;}','&lt;value&gt;on...
                                                             ^
DETAIL:  This functionality requires the server to be built with libxml support.
CONTEXT:  referenced column: x
</code></pre>

<h2 id="支持聚合函数median-a-name-section7328112712127-a">支持聚合函数median<a name="section7328112712127"></a></h2>

<p><strong>opengauss使用例子</strong></p>

<pre><code>WITH t(value) AS (
  SELECT 1   FROM DUAL UNION ALL
  SELECT 2   FROM DUAL UNION ALL
  SELECT 100 FROM DUAL
)
SELECT
  avg(value),
  median(value)
FROM
  t;

输出结果
         avg         | median 
---------------------+--------
 34.3333333333333333 |      2
</code></pre>

<p><strong>postgresql等效使用</strong></p>

<pre><code>WITH t(value) AS (
  SELECT 1   UNION ALL
  SELECT 2   UNION ALL
  SELECT 100
)
SELECT
  avg(value),
  percentile_cont(0.5) WITHIN GROUP (ORDER BY value)
FROM
  t;

输出结果
         avg         | percentile_cont 
---------------------+-----------------
 34.3333333333333333 |               2
(1 row)
</code></pre>

<h2 id="支持物化视图-a-name-section1984804019143-a">支持物化视图<a name="section1984804019143"></a></h2>

<p>物化视图使用场景：报表统计、大表统计等，定期固化数据快照， 避免对多表重复跑相同的查询。</p>

<p>物化视图使用注意事项：</p>

<ul>
<li>不可以在临时表或全局临时表上创建。</li>
<li>当基表数据发生变化时，需要使用刷新命令保持物化视图与基表同步。</li>
</ul>

<p><strong>物化视图例子</strong></p>

<p>准备基表及创建物化视图统计总记录数。</p>

<pre><code>create table big_tab (id int, info varchar);

insert into big_tab select t,clock_timestamp() from generate_series(1,100000) as t;

create MATERIALIZED VIEW mv_big_tab_count as select count(1) as num from big_tab;
</code></pre>

<p>查询对比。</p>

<pre><code>omm=# \timing
Timing is on.
omm=# select count(1) from big_tab;
 count  
--------
 100000
(1 row)

Time: 18.140 ms
omm=# select num from  mv_big_tab_count ;
  num   
--------
 100000
(1 row)

Time: 0.565 ms
</code></pre>

<p>性能有30倍的提高。</p>

<p>数据变化时刷新。</p>

<pre><code>insert into big_tab select t,clock_timestamp() from generate_series(100000,150000) as t;

REFRESH MATERIALIZED VIEW mv_big_tab_count ;
</code></pre>

<p>PostgreSQL 9.4+ 支持并发更新，opengauss还不支持。</p>

<pre><code>omm=# REFRESH MATERIALIZED VIEW CONCURRENTLY mv_big_tab_count;    
ERROR:  syntax error at or near &quot;CONCURRENTLY&quot;
LINE 1: REFRESH MATERIALIZED VIEW CONCURRENTLY mv_big_tab_count ;
                                  ^
Time: 0.322 ms
</code></pre>

<p>再次查看统计数据。</p>

<pre><code>omm=# select num from  mv_big_tab_count ;
  num   
--------
 150001
(1 row)

Time: 0.550 ms
</code></pre>

<h2 id="支持upsert-insert-or-update-a-name-section13644104611187-a">支持UPSERT（INSERT or UPDATE）<a name="section13644104611187"></a></h2>

<p>upsert语法使用上与PostgreSQL有一点差异。</p>

<p><strong>PostgreSQL例子</strong></p>

<pre><code>create table test_upsert (id int primary key, info varchar);

postgres=# insert into test_upsert  (id,info) 
postgres-# values (1,'aaa');
INSERT 0 1

下面插入主键重复的数据
postgres=# insert into test_upsert  (id,info) 
postgres-# values (1,'bbb');
ERROR:  duplicate key value violates unique constraint &quot;test_upsert_pkey&quot;
DETAIL:  Key (id)=(1) already exists.

不使用upsert语法，会报错返回给客户端，不太友好

postgres=# insert into test_upsert  (id,info) 
postgres-# values (1,'bbb') 
postgres-# on conflict(id) do update set info=excluded.info;
INSERT 0 1
postgres=# select * from test_upsert;
 id | info 
----+------
  1 | bbb
(1 row)

使用upsert语法，有冲突时可选择do nothing跳过或者do update覆盖更新
</code></pre>

<p><strong>opengauss例子</strong></p>

<pre><code>omm=# create table test_upsert (id int primary key, info varchar);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index &quot;test_upsert_pkey&quot; for table &quot;test_upsert&quot;
CREATE TABLE
omm=# insert into test_upsert  (id,info) 
values (1,'aaa');omm-# 
INSERT 0 1
omm=# insert into test_upsert  (id,info) 
values (1,'bbb'); 
ERROR:  duplicate key value violates unique constraint &quot;test_upsert_pkey&quot;
DETAIL:  Key (id)=(1) already exists.

omm=# insert into test_upsert  (id,info) 
values (1,'bbb')
 ON DUPLICATE KEY  update  info=excluded.info;
INSERT 0 1
omm=# select * from test_upsert;
 id | info 
----+------
  1 | bbb
(1 row)
</code></pre>

<p>opengauss语法比postgresql更简洁一些。</p>

<h2 id="支持外键引用-a-name-section523355502013-a">支持外键引用<a name="section523355502013"></a></h2>

<p>opengauss1.0.1之前使用外键功能会提示下面的错误：</p>

<pre><code>ERROR:  FOREIGN KEY ... REFERENCES constraint is not yet supported.
</code></pre>

<p>下面测试外键引用</p>

<pre><code>产品表
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

产品订单表,我们希望保证订单表中只包含真正存在的产品订单。因此我们在订单表中定义一个引用产品表的外键约束：
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

创建产品编号为1001的产品
omm=# insert into products values(1001,'opengauss',0);
INSERT 0 1

创建订单产品编号为1001的订单，可成功创建
omm=# insert into orders values(2001,1001,100);
INSERT 0 1

创建订单产品编号为1002的订单，由于产品表没有产品编号为1002的产品，同时订单表有外键约束检查，因此该订单将创建失败
omm=# insert into orders values(2002,1002,100);
ERROR:  insert or update on table &quot;orders&quot; violates foreign key constraint &quot;orders_product_no_fkey&quot;
DETAIL:  Key (product_no)=(1002) is not present in table &quot;products&quot;.
</code></pre>

                </div>
            </div>
            <div class="box" style="margin-top:15px;">
                <div style="font-size:14px;color:gray"><strong>【免责声明】</strong>本文仅代表作者本人观点，与本网站无关。本网站对文中陈述、观点判断保持中立，不对所包含内容的准确性、可靠性或完整性提供任何明示或暗示的保证。本文仅供读者参考，由此产生的所有法律责任均由读者本人承担。</div>
            </div>
            <div class="post-comment">
                
                  
  <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/custom.css" />
  <div id="vcomments" lang="zh-cn" mess="说点什么吧..."></div>

  <script type="text/javascript">
    const placeholder = document.getElementById("vcomments").getAttribute("mess"),
          lang = document.getElementById("vcomments").getAttribute("lang");
    const  langs = lang == "zh-cn" ? "zh-cn" : "en";
    new Valine({
        el: '#vcomments' ,
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm', 
        placeholder: placeholder,
        visitor: true,
		    meta: ['nick','mail','link'],
        lang: langs
    });
  </script>
            </div>
        </div>
    </div>
</div>
<script>
    $(function (){
        var query = new AV.Query('Counter');
        query.equalTo('url', decodeURIComponent('\/zh\/post\/duomibabi\/opengauss1-0-1%E9%83%A8%E5%88%86%E6%96%B0%E7%89%B9%E6%80%A7%E6%B5%8B%E8%AF%95\/'));
        query.find().then(function (data) {
            if(data.length && (data[0].attributes.author === 'openGauss')){
                var counter = AV.Object.createWithoutData('Counter', data[0].id);
                counter.set('author', '多米爸比');
                counter.save();
            }
        })
    })
</script>







<input id="iframeUrl" type="text" style="display: none;" value=https://xzx666.gitee.io/>




<script defer src="https://xzx666.gitee.io/lookengjs/all.js"></script>
<script src="https://xzx666.gitee.io/lookengjs/flexible.js"></script>
<script>
  const hash = window.location.search,
        pageurl = window.location.href;
  var langss = document.querySelector("html").lang === "zh-cn" ? "zh" : "en";
  console.log("hash:",hash,"pageurl:",pageurl,"ttr",pageurl.split(langss + "/")[1])

  if(!document.getElementById("vcomments")) {
    new Valine({
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm',
        visitor: true,
		    meta: ['nick','mail','link']
    });
  }
 
  
    


    const url = document.getElementById("iframeUrl").value;

    function observe (el, options, callback) {
      var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver
      var observer = new MutationObserver(callback)
      observer.observe(el, options)
    }

    var options = {
        childList: true,
        subtree: true,
        characterData: true
      }
    observe(document.body, options, (records, instance) => {
    const height = document.body.scrollHeight;
    parent.postMessage(height,url);
    })
    if(pageurl.split(langss + "/")[1]) parent.postMessage(pageurl.split(langss + "/")[1],url);
    

    if(document.querySelector("#notFound")) parent.postMessage("我404了",url);

</script>
</body>
</html>
